import datetime
import logging
import os

import mistune
import pandas as pd
from bs4 import BeautifulSoup
from flask import Flask, render_template, request, redirect, url_for, flash, session, send_file, jsonify
import sqlite3
from flask_socketio import SocketIO
from flask_wtf import FlaskForm
from openai import OpenAI
from wtforms import StringField, PasswordField, SubmitField
from wtforms.fields.choices import SelectField
from wtforms.fields.datetime import DateField
from wtforms.fields.numeric import FloatField
from wtforms.fields.simple import TextAreaField, BooleanField
from wtforms.validators import DataRequired, Length, EqualTo
from flask_login import UserMixin, LoginManager, current_user, login_user, logout_user, login_required
from flask import abort

# 创建Flask应用实例
app = Flask(__name__)
# 加载配置文件
app.config.from_object('config')
# 设置应用的密钥，用于会话管理等安全相关操作
app.config['SECRET_KEY'] = 'your-secret-key'
# 设置会话的保护级别为强，确保会话数据在传输过程中不会被篡改
# app.config['SESSION_COOKIE_SECURE'] = True
# app.config['SESSION_COOKIE_HTTPONLY'] = True
# app.config['SESSION_COOKIE_SAMESITE'] = 'Strict'
app.config['PERMANENT_SESSION_LIFETIME']  = datetime.timedelta(minutes=10)

# 初始化SocketIO，用于支持实时通信
socketio = SocketIO(app)

# 初始化 LoginManager，用于处理用户登录管理
login_manager = LoginManager()
login_manager.init_app(app)
# 设置登录视图，当用户未登录访问需要登录的页面时，会重定向到该视图
login_manager.login_view = 'login'
login_manager.login_message  = '请先登录以访问此页面。'
login_manager.login_message_category  = 'info'

class User(UserMixin):
    def __init__(self, id, username, password, is_admin):
        self.id = id
        self.username = username
        self.password = None
        self.is_admin = is_admin

# 用户加载回调函数，用于从用户ID加载用户对象
def load_admin_user():
    return User(0, 'admin', 'admin', 1)

def load_user_by_id(user_id):
    conn = sqlite3.connect('children_doctor.db')
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users WHERE id =?", (user_id,))
    user_data = cursor.fetchone()
    conn.close()
    if user_data:
        return User(user_data[0], user_data[1], user_data[2], 0)
    return None

@login_manager.user_loader
def load_user(user_id):
    if(session.get('username')=="admin"):
        return load_admin_user()
    else:
        return load_user_by_id(user_id)

# 表单类，以下定义了多个表单类，用于不同的页面表单处理
class EditUserForm(FlaskForm):
    username = StringField('用户名', validators=[DataRequired(), Length(min=1, max=25)])
    is_disable = BooleanField('禁用用户')
    is_admin = BooleanField('设为管理员')
    submit = SubmitField('保存更改')

# 营养分类建议录入表单
class KnowledgeItemForm(FlaskForm):
    name = StringField('分类名称', validators=[DataRequired()])
    description = StringField('描述')
    suitable_gender = StringField('适用性别')
    suitable_age = FloatField('适合年龄', validators=[DataRequired()])  # 添加 suitable_age 字段
    suggestions = TextAreaField('建议')
    recommended_products = TextAreaField('推荐产品')
    submit = SubmitField('提交')

class RegistrationForm(FlaskForm):
    username = StringField('用户名', validators=[DataRequired(), Length(min=1, max=25)])
    password = PasswordField('密码', validators=[DataRequired(), Length(min=1, max=120)])
    confirm_password = PasswordField('确认密码',
                                     validators=[DataRequired(), EqualTo('password', message='密码必须匹配')])
    submit = SubmitField('注册')


class LoginForm(FlaskForm):
    username = StringField('用户名', validators=[DataRequired()])
    password = PasswordField('密码', validators=[DataRequired()])
    submit = SubmitField('登录')


# 管理注册表单类
class AdminRegistrationForm(FlaskForm):
    username = StringField('用户名', validators=[DataRequired(), Length(min=1, max=25)])
    password = PasswordField('密码', validators=[DataRequired(), Length(min=1, max=120)])
    submit = SubmitField('管理注册')


# 添加或编辑后，保存儿童信息表单类
class ChildForm(FlaskForm):
    name = StringField('姓名', validators=[DataRequired()])
    birth_date = DateField('出生日期', validators=[DataRequired()], format='%Y-%m-%d')
    # 将性别改为选择字段
    gender = SelectField('性别', choices=[('男', '男'), ('女', '女')], validators=[DataRequired()])
    height = FloatField('身高(厘米)', validators=[DataRequired()])
    weight = FloatField('体重(千克)', validators=[DataRequired()])
    submit = SubmitField('保存信息')


# 管理员登录表单类
class AdminLoginForm(FlaskForm):
    username = StringField('用户名', validators=[DataRequired()])
    password = PasswordField('密码', validators=[DataRequired()])
    submit = SubmitField('登录')


class Pagination:
    curPage=0
    perPage=0
    totalPage=0
    def __init__(self,curPage,perPage,totalPage):
        self.curPage=curPage
        self.perPage=perPage
        self.totalPage=totalPage

# 根路由，处理根路径的请求
@app.route('/')
def root():
    return redirect(url_for('login'))

# 用户主页面路由，需要用户已登录才能访问
@app.route('/user_main')
@login_required  # 确保用户已登录才能访问该页面
def user_main():
    try:
        user_id = current_user.id
        conn = get_db_connection()

        # 查询此用户的小孩信息
        children = conn.execute('SELECT * FROM children WHERE user_id = ?', (user_id,)).fetchall()
        paired_info = []
        for child in children:
            birth_date = datetime.datetime.strptime(child['birth_date'], '%Y-%m-%d')
            current_date = datetime.datetime.now()
            age_in_months = (current_date.year - birth_date.year) * 12 + (current_date.month - birth_date.month) + 1
            knowledge = conn.execute('SELECT * FROM KnowledgeItems WHERE suitable_age = ?', (age_in_months,)).fetchall()
            paired_info.append((child, knowledge))

        user_info = {
            'username': current_user.username,
            'is_admin': current_user.is_admin
        }

        return render_template('/user/main.html', user_info=user_info, paired_info=paired_info)
    except Exception as e:
        # 记录错误日志
        logging.error(f"渲染模板时出现错误: {str(e)}")
        # 返回 500 错误
        abort(500, description="服务器内部错误，请稍后再试。")
    finally:
        if conn:
            conn.close()

# 用户注册路由，支持GET和POST请求
@app.route('/register', methods=['GET', 'POST'])
def register():
    form = RegistrationForm()
    if form.validate_on_submit():
        username = form.username.data
        password = form.password.data
        conn = sqlite3.connect('children_doctor.db')
        cursor = conn.cursor()
        try:
            cursor.execute("INSERT INTO users (username, password, is_admin) VALUES (?,?,?)",
                           (username, password, False))
            conn.commit()
            flash('注册成功，请登录', 'success')
            return redirect(url_for('login'))
        except sqlite3.IntegrityError:
            flash('用户名已存在，请选择其他用户名', 'danger')
        finally:
            conn.close()
    return render_template('/user/register.html', form=form)


# 用户登录路由，支持GET和POST请求
@app.route('/login', methods=['GET', 'POST'])
def login():
    form = LoginForm()
    if form.validate_on_submit():
        username = form.username.data
        password = form.password.data
        conn = sqlite3.connect('children_doctor.db')
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM users WHERE username =? AND password =?", (username, password))
        user_data = cursor.fetchone()
        conn.close()
        if user_data:
            user = User(user_data[0], user_data[1], user_data[2], user_data[3])
            login_user(user)
            session['username'] = username  # 存储用户名到 session
            return redirect(url_for('user_main'))
        else:
            flash('用户名或密码错误', 'danger')
    return render_template('/user/login.html', form=form)

# 管理员仪表盘路由
@app.route('/admin_dashboard')
@login_required
def admin_dashboard():
    return render_template('/admin/admin_dashboard.html')

# 管理员注册路由，支持GET和POST请求
@app.route('/admin_register', methods=['GET', 'POST'])
def admin_register():
    form = AdminRegistrationForm()
    if form.validate_on_submit():
        username = form.username.data
        password = form.password.data
        conn = sqlite3.connect('children_doctor.db')
        cursor = conn.cursor()
        try:
            cursor.execute("INSERT INTO users (username, password, is_admin) VALUES (?,?,?)",
                           (username, password, True))
            conn.commit()
            flash('管理注册成功', 'success')
            return redirect(url_for('admin_dashboard'))
        except sqlite3.IntegrityError:
            flash('用户名已存在，请选择其他用户名', 'danger')
        finally:
            conn.close()
    return render_template('/admin/admin_register.html', form=form)


# 用户注销路由，需要用户已登录才能访问
@app.route('/logout')
@login_required
def logout():
    """
    用户和管理员公用的注销处理函数
    :return: 处理注销后重定向到相应页面
    """
    # 判断是否是管理员
    if session.get('username') != 'admin':
        logout_user()
        logging.info('已退出登录', 'success')
        session.pop('username', None)
        session.clear()
        return redirect(url_for('login'))
    else:
        logout_user()
        logging.info('管理员退出登录', 'success')
        session.pop('username', None)
        session.clear()
        return redirect(url_for('admin_login'))

# 查看儿童信息路由，支持GET请求
@app.route('/view_children', methods=['GET'])
@login_required
def view_children():
    """
    查看儿童信息处理函数
    :return: 渲染后的查看儿童信息页面
    """
    # 获取当前页码，默认为第 1 页
    page = request.args.get('page', 1, type=int)
    # 每页显示的儿童数量
    per_page = 3
    # 计算偏移量
    offset = (page - 1) * per_page
    # 获取搜索关键词
    search = request.args.get('search', '')

    conn = get_db_connection()
    if search:
        query = 'SELECT * FROM children WHERE name LIKE? LIMIT? OFFSET?'
        children = conn.execute(query, ('%' + search + '%', per_page, offset)).fetchall()
        total_count = conn.execute('SELECT COUNT(*) FROM children WHERE name LIKE?', ('%' + search + '%',)).fetchone()[0]
    else:
        children = conn.execute('SELECT * FROM children LIMIT? OFFSET?', (per_page, offset)).fetchall()
        total_count = conn.execute('SELECT COUNT(*) FROM children').fetchone()[0]
    total_pages = (total_count + per_page - 1) // per_page
    has_prev = page > 1
    has_next = page < total_pages
    conn.close()
    return render_template('/user/view_children.html', children=children, page=page, total_pages=total_pages, has_prev=has_prev, has_next=has_next, search=search)

# 添加儿童信息路由，支持GET和POST请求
@app.route('/add_child', methods=['GET', 'POST'])
@login_required
def add_child():
    form = ChildForm()
    if form.validate_on_submit():
        user_id = current_user.id
        name = form.name.data
        birth_date = form.birth_date.data.strftime('%Y-%m-%d')
        gender = form.gender.data
        height = form.height.data
        weight = form.weight.data

        conn = sqlite3.connect('children_doctor.db')
        cursor = conn.cursor()
        try:
            cursor.execute("INSERT INTO children (user_id, name, birth_date, gender, height, weight) VALUES (?,?,?,?,?,?)",
                           (user_id, name, birth_date, gender, height, weight))
            conn.commit()
            flash('儿童信息添加成功', 'success')
            logging.info(f"用户 {current_user.username} 添加儿童信息 {name} 成功")
            return redirect(url_for('view_children'))
        except Exception as e:
            flash(f'添加儿童信息失败: {str(e)}', 'danger')
            logging.error(f"用户 {current_user.username} 添加儿童信息 {name} 失败: {str(e)}")
        finally:
            conn.close()
    return render_template('/user/add_child.html', form=form)


@app.route('/admin_login', methods=['GET', 'POST'])
def admin_login():
    form = AdminLoginForm()
    if form.validate_on_submit():
        username = form.username.data
        password = form.password.data
        if username == app.config['ADMIN_USERNAME'] and password == app.config['ADMIN_PASSWORD']:
            user = User(0, username, password, True)
            login_user(user)
            session['username'] = 'admin'  # 存储用户名到 session
            current_user.is_admin = True
            logging.info(f"管理员 {username} 登录成功")
            return redirect(url_for('admin_dashboard'))
        else:
            flash('管理员用户名或密码错误', 'danger')
            logging.warning(f"管理员 {username} 登录失败，用户名或密码错误")
    return render_template('/admin/admin_login.html', form=form)

# 初始化数据库连接函数
def get_db_connection():
    conn = sqlite3.connect('children_doctor.db')
    conn.row_factory = sqlite3.Row
    return conn

# 新增路由，处理修改页面
@app.route('/edit_child/<int:child_id>', methods=['GET', 'POST'])
@login_required
def edit_child(child_id):
    """
    编辑儿童信息处理函数
    :param child_id: 要编辑的儿童信息的ID
    :return: 根据请求方法和处理结果返回相应页面
    """
    try:
        # 从数据库中查询指定ID的儿童信息
        child = get_db_connection().execute('SELECT * FROM children WHERE id = ?', (child_id,)).fetchone()
        # 创建儿童信息表单实例
        form = ChildForm()
        if form.validate_on_submit():
            # 处理表单提交，更新数据库
            name = form.name.data
            # 将日期对象转换为字符串格式
            birth_date = form.birth_date.data.strftime('%Y-%m-%d')
            gender = form.gender.data
            height = form.height.data
            weight = form.weight.data

            # 获取数据库连接
            conn = get_db_connection()
            try:
                # 执行更新操作
                conn.execute('UPDATE children SET name = ?, birth_date = ?, gender = ?, height = ?, weight = ? WHERE id = ?',
                             (name, birth_date, gender, height, weight, child_id))
                # 提交事务
                conn.commit()
                # 记录日志
                logging.info(f"用户 {current_user.username} 更新儿童信息 {name} 成功")
                # 重定向到查看儿童信息页面
                return redirect(url_for('view_children'))
            except Exception as e:
                # 显示错误信息
                flash(f'更新儿童信息失败: {str(e)}', 'danger')
                # 记录错误日志
                logging.error(f"用户 {current_user.username} 更新儿童信息 {name} 失败: {str(e)}")
            finally:
                # 关闭数据库连接
                conn.close()
        elif request.method == 'GET':
            # 填充表单数据
            form.name.data = child['name']
            # 使用datetime.datetime.strptime解析日期字符串
            form.birth_date.data = datetime.datetime.strptime(child['birth_date'], '%Y-%m-%d')
            form.gender.data = child['gender']
            form.height.data = child['height']
            form.weight.data = child['weight']

        # 渲染编辑儿童信息页面
        return render_template('/user/edit_child.html', form=form, child=child)
    except Exception as e:
        # 记录错误日志
        logging.error(f"编辑儿童信息时出现错误: {str(e)}")
        # 显示错误信息
        flash(f'编辑儿童信息时出现错误: {str(e)}', 'danger')
        # 重定向到查看儿童信息页面
        return redirect(url_for('view_children'))

# 欢迎页面路由
@app.route('/welcome')
@login_required
def welcome():
    return render_template('/admin/welcome.html')

# 首页路由，展示用户列表
@app.route('/users', methods=['GET'])
@login_required
def users():
    curPage = request.args.get('curPage', 1, type=int)
    if curPage < 1:
        curPage = 1
    perPage = 5
    offset = (curPage - 1) * perPage

    conn = get_db_connection()
    curPageItems = conn.execute('SELECT * FROM users LIMIT? OFFSET?', (perPage, offset)).fetchall()
    totalItemsNumber = conn.execute('SELECT COUNT(*) FROM users').fetchone()[0]
    conn.close()

    totalPages = (totalItemsNumber + perPage - 1) // perPage
    pagination = Pagination(curPage, perPage, totalPages)
    return render_template('/admin/view_users.html', curPageItems=curPageItems, pagination=pagination)


# 编辑用户信息路由，支持管理员和普通用户编辑
@app.route('/admin/edit_user/<int:user_id>', methods=['GET', 'POST'])
@app.route('/edit_user/<int:user_id>', methods=['GET', 'POST'])
@login_required
def edit_user(user_id):
    form = EditUserForm()
    if form.validate_on_submit():
        # 处理表单提交，更新数据库
        username = form.username.data
        is_disable = form.is_disable.data
        is_admin = form.is_admin.data

        try:
            conn = get_db_connection()
            conn.execute('UPDATE users SET username = ?, is_disable = ?, is_admin = ? WHERE id = ?',
                         (username, is_disable, is_admin, user_id))
            conn.commit()
            flash('用户信息更新成功', 'success')
        except Exception as e:
            # 处理数据库更新出错的情况
            flash(f'用户信息更新失败: {str(e)}', 'danger')
        finally:
            if conn:
                conn.close()

        return redirect(url_for('users'))
    elif request.method == 'GET':
        try:
            conn = get_db_connection()
            user = conn.execute('SELECT * FROM users WHERE id = ?', (user_id,)).fetchone()
            if user:
                form.username.data = user['username']
                form.is_disable.data = user['is_disable']
                form.is_admin.data = user['is_admin']
        except Exception as e:
            # 处理数据库查询出错的情况
            flash(f'获取用户信息失败: {str(e)}', 'danger')
        finally:
            if conn:
                conn.close()

    return render_template('/admin/edit_user.html', form=form, user=user if 'user' in locals() else None)


@app.route('/delete_user/<int:user_id>', methods=['GET'])
@login_required
def delete_user(user_id):
    try:
        conn = get_db_connection()
        # 删除用户记录
        conn.execute('DELETE FROM users WHERE id = ?', (user_id,))
        conn.commit()
        flash('用户删除成功', 'success')
        logging.info(f"用户 ID {user_id} 删除成功")
    except Exception as e:
        flash(f'用户删除失败: {str(e)}', 'danger')
        logging.error(f"用户 ID {user_id} 删除失败: {str(e)}")
    finally:
        if conn:
            conn.close()

    return redirect(url_for('users'))

@app.route('/view_knowledge_items', methods=['GET', 'POST'])
@login_required
def view_knowledge_items():
    curPage = request.args.get('curPage', 1, type=int)
    perPage = 5
    offset = (curPage - 1) * perPage

    conn = get_db_connection()
    # 计算总记录数
    totalItemsNumber = conn.execute('SELECT COUNT(*) FROM KnowledgeItems').fetchone()[0]
    # 获取当前页的数据
    curPageItems = conn.execute('SELECT * FROM KnowledgeItems LIMIT? OFFSET?', (perPage, offset)).fetchall()
    conn.close()


    totalPages = (totalItemsNumber + perPage - 1) // perPage
    pagination = Pagination(curPage, perPage, totalPages)
    return render_template('/admin/view_knowledge_items.html', curPageItems=curPageItems, pagination=pagination)


# 新增 add_knowledge 函数
@app.route('/add_new_knowledge', methods=['GET', 'POST'])
@login_required
def add_new_knowledge():
    form = KnowledgeItemForm()
    if form.validate_on_submit():
        name = form.name.data
        description = form.description.data
        suitable_gender = form.suitable_gender.data
        suitable_age = form.suitable_age.data
        suggestions = form.suggestions.data
        recommended_products = form.recommended_products.data

        conn = get_db_connection()
        try:
            conn.execute('INSERT INTO KnowledgeItems (name, description, suitable_gender, suitable_age, suggestions, recommended_products) VALUES (?,?,?,?,?,?)',
                         (name, description, suitable_gender, suitable_age, suggestions, recommended_products))
            conn.commit()
            flash('营养知识添加成功', 'success')
            return redirect(url_for('view_knowledge_items'))
        except Exception as e:
            flash(f'添加营养知识失败: {str(e)}', 'danger')
        finally:
            conn.close()
    pagination = Pagination(1, 10, 1)
    return render_template('/admin/add_new_knowledge.html', form=form, pagination=pagination)

# 编辑知识条目路由，支持GET和POST请求
@app.route('/edit_knowledge/<int:knowledge_id>', methods=['GET', 'POST'])
@login_required
def edit_knowledge(knowledge_id):
    form = KnowledgeItemForm()
    if form.validate_on_submit():
        # 获取表单数据
        name = form.name.data
        description = form.description.data
        suitable_gender = form.suitable_gender.data
        suitable_age = form.suitable_age.data
        suggestions = form.suggestions.data
        recommended_products = form.recommended_products.data

        conn = get_db_connection()
        try:
            # 更新数据库中的知识信息
            conn.execute('UPDATE KnowledgeItems SET name = ?, description = ?, suitable_gender = ?, suitable_age = ?, suggestions = ?, recommended_products = ? WHERE id = ?',
                         (name, description, suitable_gender, suitable_age, suggestions, recommended_products, knowledge_id))
            conn.commit()
            flash('知识更新成功', 'success')
            return redirect(url_for('view_knowledge_items'))
        except Exception as e:
            flash(f'更新知识失败: {str(e)}', 'danger')
        finally:
            conn.close()
    else:
        # 获取数据库连接
        conn = get_db_connection()
        # 根据知识 ID 查询知识信息
        knowledge = conn.execute('SELECT * FROM KnowledgeItems WHERE id = ?', (knowledge_id,)).fetchone()
        conn.close()

        if not knowledge:
            # 如果未找到知识，返回 404 错误
            abort(404)
        # 填充表单初始值
        form.name.data = knowledge['name']
        form.description.data = knowledge['description']
        form.suitable_gender.data = knowledge['suitable_gender']
        form.suitable_age.data = knowledge['suitable_age']
        form.suggestions.data = knowledge['suggestions']
        form.recommended_products.data = knowledge['recommended_products']

        return render_template('/admin/edit_knowledge_item.html', form=form, knowledge_id=knowledge_id)


@app.route('/common_sense_management', methods=['GET', 'POST'])
@login_required
def common_sense_management():
    """
    常识管理处理函数
    :return: 根据请求方法和处理结果返回相应页面
    """
    try:
        if request.method == 'POST':
            # 获取表单提交的新常识信息
            new_common_sense = request.form.get('common_sense')
            # 保存新常识信息
            save_common_sense(new_common_sense)
            # 重定向到常识管理页面
            return redirect(url_for('common_sense_management'))

        # 获取当前常识信息
        current_common_sense = get_common_sense()
        # 渲染常识管理页面
        return render_template('/admin/common_sense_management.html', common_sense=current_common_sense)
    except Exception as e:
        # 记录错误日志
        logging.error(f"常识管理页面出现错误: {str(e)}")
        # 返回错误信息
        return "常识管理页面出现错误", 500
    finally:
        pass

# 假设常识信息保存在一个文本文件中
COMMON_SENSE_FILE = 'common_sense.txt'

# 从文本文件中读取常识信息函数
def read_common_sense_from_txt():
    """
    从文本文件中读取常识信息
    :return: 常识信息字符串
    """
    try:
        with open(COMMON_SENSE_FILE, 'r', encoding='utf-8') as f:
            return f.read()
    except FileNotFoundError:
        return None
    except Exception as e:
        logging.error(f"读取常识文件时出现错误: {str(e)}")
        return None

# 获取常识信息函数
def get_common_sense():
    """
    获取常识信息
    :return: 常识信息字符串
    """
    # 读取常识信息
    common_sense = read_common_sense_from_txt()
    if common_sense is None:
        common_sense = USER_COMMON_SENSE

    common_sense = common_sense.replace('\n\n', '\n')
    return common_sense

# 保存常识信息函数
def save_common_sense(common_sense):
    if common_sense is None:
        common_sense = USER_COMMON_SENSE
    common_sense=common_sense.replace('\r\n', '\n')
    with open(COMMON_SENSE_FILE, 'w', encoding='utf-8') as f:
        f.write(common_sense)        
		
@app.route('/delete_knowledge/<int:knowledge_id>', methods=['GET'])
@login_required
def delete_knowledge(knowledge_id):
    """
    删除知识条目处理函数
    :param knowledge_id: 要删除的知识条目ID
    :return: 处理删除后返回相应信息
    """
    conn = None
    try:
        conn = get_db_connection()
        # 删除知识库记录
        conn.execute('DELETE FROM KnowledgeItems WHERE id = ?', (knowledge_id,))
        conn.commit()
        flash('知识库项删除成功', 'success')
        logging.info(f"知识库项 ID {knowledge_id} 删除成功")
    except Exception as e:
        flash(f'知识库项删除失败: {str(e)}', 'danger')
        logging.error(f"知识库项 ID {knowledge_id} 删除失败: {str(e)}")
    finally:
        if conn:
            # 关闭数据库连接
            conn.close()

    return redirect(url_for('view_knowledge_items'))


# 导入知识库从 Excel，1
# @app.route('/import_knowledge_from_excel', methods=['POST'])
# def import_knowledge_from_excel():
#     conn = None
#     try:
#         file = request.files.get('excel_file')
#         if not file:
#             logging.info("未选择文件，导入操作终止")
#             return "未选择文件"
#         # 读取 Excel 文件
#         df = pd.read_excel(file)
#         # 获取知识类别（第一行作为列名）
#         knowledge_categories = df.columns[1:]
#         # 初始化数据库连接
#         conn = sqlite3.connect('children_doctor.db')
#         cursor = conn.cursor()
#         # 遍历每一行数据
#         for index, row in df.iterrows():
#             month_age = row[0]
#             for category in knowledge_categories:
#                 knowledge_content = row[category]
#                 if pd.notna(knowledge_content):
#                     # 插入数据到数据库
#                     cursor.execute('''
#                         INSERT INTO KnowledgeItems (suitable_age, name, description)
#                         VALUES (?,?,?)
#                     ''', (month_age, category, knowledge_content))
#         # 提交事务
#         conn.commit()
#         logging.info("数据导入成功")
#         return "数据导入成功"
#     except Exception as e:
#         logging.error(f"导入数据时出现错误: {str(e)}")
#         return f"导入数据时出现错误: {str(e)}"
#     finally:
#         if conn:
#             conn.close()

@app.route('/import_knowledge_from_excel', methods=['POST'])
@login_required
def import_knowledge_from_excel():
    conn = sqlite3.connect('children_doctor.db')
    try:
        # 获取上传的Excel文件
        file = request.files.get('excel_file')
        if not file:
            # 记录日志
            logging.info("未选择文件，导入操作终止")
            return "未选择文件"

        # 读取 Excel 文件
        df = pd.read_excel(file)

        # 初始化数据库连接
        cursor = conn.cursor()

        # 遍历 DataFrame 中的每一行
        for index, row in df.iterrows():
            name = row.get('name')
            description = row.get('description')
            suitable_gender = row.get('suitable_gender')
            suitable_age = row.get('suitable_age')
            suggestions = row.get('suggestions')
            recommended_products = row.get('recommended_products')

            # 插入数据到数据库
            cursor.execute('''
                INSERT INTO KnowledgeItems (name, description, suitable_gender, suitable_age, suggestions, recommended_products)
                VALUES (?,?,?,?,?,?)
            ''', (name, description, suitable_gender, suitable_age, suggestions, recommended_products))

        # 提交事务
        conn.commit()
        # 记录日志
        logging.info("数据导入成功")
        return "数据导入成功"
    except Exception as e:
        # 记录错误日志
        logging.error(f"导入数据时出现错误: {str(e)}")
        return f"导入数据时出现错误: {str(e)}"
    finally:
        if conn:
            # 关闭数据库连接
            conn.close()

# 导出知识库到 Excel
@app.route('/export_knowledge_to_excel')
@login_required
def export_knowledge_to_excel():
    """
    导出知识库到Excel文件处理函数
    :return: 导出的Excel文件
    """
    conn = None
    try:
        # 获取数据库连接
        conn = sqlite3.connect('children_doctor.db')
        cursor = conn.cursor()
        # 查询知识条目数据
        cursor.execute('SELECT * FROM KnowledgeItems')
        data = cursor.fetchall()
        # 获取列名
        columns = [description[0] for description in cursor.description]
        # 创建DataFrame
        df = pd.DataFrame(data, columns=columns)
        # 导出为Excel文件
        excel_file = 'knowledge_export.xlsx'
        df.to_excel(excel_file, index=False)
        # 返回Excel文件供下载
        return send_file(excel_file, as_attachment=True)
    except Exception as e:
        # 记录错误日志
        logging.error(f"导出数据时出现错误: {str(e)}")
        return f"导出数据时出现错误: {str(e)}"
    finally:
        if conn:
            # 关闭数据库连接
            conn.close()


# 导入导出页面路由
@app.route('/import_export_knowledge')
@login_required
def import_export_knowledge():
    try:
        return render_template('/admin/import_export_knowledge.html')
    except Exception as e:
        return f"加载页面时出现错误: {str(e)}"

from flask import render_template
from config import USER_COMMON_SENSE

# 常识页面路由，需要用户已登录才能访问
@app.route('/common_sense')
@login_required
def common_sense():
    """
    常识页面处理函数
    :return: 渲染后的常识页面
    """
    try:
        # 获取常识信息
        common_sense = get_common_sense()
        # 渲染常识页面
        return render_template('/user/common_sense.html', common_sense=common_sense)
    except Exception as e:
        # 记录错误日志
        logging.error(f"获取常识信息时出现错误: {str(e)}")
        # 返回错误信息
        return "获取常识信息时出现错误", 500
    finally:
        pass

# 清空知识库路由
@app.route('/clear_knowledge')
@login_required
def clear_knowledge():
    """
    清空知识库处理函数
    :return: 清空结果信息
    """
    conn = None
    try:
        # 获取数据库连接
        conn = sqlite3.connect('children_doctor.db')
        cursor = conn.cursor()
        # 执行清空操作
        cursor.execute('DELETE FROM KnowledgeItems')
        # 提交事务
        conn.commit()
        # 记录日志
        logging.info("知识库已清空")
        return "知识库已清空"
    except Exception as e:
        # 记录错误日志
        logging.error(f"清空知识库时出现错误: {str(e)}")
        return f"清空知识库时出现错误: {str(e)}", 500
    finally:
        if conn:
            # 关闭数据库连接
            conn.close()

# 与AI聊天页面路由
@app.route('/chatWithAI')
@login_required
def chatWithAI():
    return render_template('user/chatWithAI.html')


# 聊天接口路由，支持POST请求
@app.route('/chat', methods=['POST'])
@login_required
def chat():
    message = request.json.get('message')
    client = OpenAI(
        # 若没有配置环境变量，请用百炼API Key将下行替换为：api_key="sk-xxx",
        api_key=os.getenv("DASHSCOPE_API_KEY"),
        # 如何获取API Key：https://help.aliyun.com/zh/model-studio/developer-reference/get-api-key
        base_url="https://dashscope.aliyuncs.com/compatible-mode/v1"
    )

    # 调用模型生成回复
    prompt = "你是一名儿童保健医生，回答70字以内"
    try:
        completion = client.chat.completions.create(
            model="qwen-plus",
            # model="deepseek-r1",
            max_tokens=70,
            # max_completion_tokens=100,
            # stop=["\n","。"],
            messages=[
                {'role': 'user', 'content': message},
                {'role': 'assistant', 'content': prompt}
            ])

        if completion:
            ai_response = completion.choices[0].message.content
            logging.info(f"用户消息: {message}, AI回复: {ai_response}")
            # 将 Markdown 转换为 HTML
            markdown = mistune.create_markdown()
            html = markdown(ai_response)
            # 使用 BeautifulSoup 提取纯文本
            soup = BeautifulSoup(html, 'html.parser')
            plain_text = soup.get_text()

            return jsonify({'response': plain_text})
    except Exception as e:
        logging.error(f"API请求失败: {str(e)}")
        return jsonify({'error': 'API请求失败'}), 500


if __name__ == '__main__':
    # 连接数据库
    conn = sqlite3.connect('children_doctor.db')
    cursor = conn.cursor()
    # 创建用户表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password TEXT NOT NULL,
            is_disable BOOLEAN DEFAULT 0,
            is_admin BOOLEAN DEFAULT 0
        )
    ''')
    # 创建儿童表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS children (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            name TEXT NOT NULL,
            birth_date TEXT NOT NULL,
            gender TEXT NOT NULL,
            height REAL,
            weight REAL,
            FOREIGN KEY (user_id) REFERENCES users(id)
        )
    ''')
    # 创建知识表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS KnowledgeItems (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            description TEXT,
            suitable_age REAL,
            suitable_gender TEXT,
            suggestions TEXT,
            recommended_products TEXT
        )
    ''')
    # 提交事务
    conn.commit()
    # 记录日志
    logging.info("数据库表创建成功")
    # 关闭数据库连接
    conn.close()
    # 启动SocketIO服务器
    socketio.run(app, host='0.0.0.0', port=5001, allow_unsafe_werkzeug=True, debug=True)